package app;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Properties;

public class E823SimpleInsert {

    /**
     * @param args
     * @throws SQLException
     * @throws IOException
     */
    public static void main(String[] args) throws IOException, SQLException {
	// Connection con = getConnection();
	// String sql = "INSERT INTO customer (name, address, ";
	// sql += " email, ccNo, ccType, maturity) values(";
	// sql += " 'Clar Nelis', 'Vosselaar st. 19, Trnaut, Belgium', ";
	// sql += " 'Clar@adw.com', 	'11345694671231', ";
	// sql += " 'MasterCard', '2014-07-31') ";
	// Statement stmt = con.createStatement();
	// stmt.executeUpdate(sql);

	// Date date= new Date(112, 03, 31);
	// addCustomer("Petya", "Voronezj", "petya@gmail.com",
	// "4299999999999999", "MasterCard", date);
	// con.close();

	// getMerchantTotal(1);

//	List<Merchant> merchants = getMerchants();
//	for (Merchant m : merchants) {
//	    System.out.println(m.toString());
	
	addPayment(new Date(112,1,14), 2, 2, "PIROJOK", 2.0);
	}

    

    public static Connection getConnection() throws IOException, SQLException {
	Connection conn = null;
	Properties props = new Properties();
	InputStreamReader in = new InputStreamReader(new FileInputStream(
		"appProperties.txt"), "UTF-8");
	props.load(in);
	in.close();

	String connString = props.getProperty("DBConnectionString");
	conn = DriverManager.getConnection(connString);
	return conn;
    }

    public static void addCustomer(String name, String address, String email,
	    String ccNo, String ccType, java.util.Date dt) throws SQLException,
	    IOException {
	Connection con = getConnection();

	String sql = "INSERT INTO customer (name, address, ";
	sql += " email, ccNo, ccType, maturity) values(?,?,?,?,?,?) ";
	PreparedStatement stmt = con.prepareStatement(sql);

	java.sql.Date dt1 = new java.sql.Date(dt.getTime());

	stmt.setString(1, name);
	stmt.setString(2, address);
	stmt.setString(3, email);
	stmt.setString(4, ccNo);
	stmt.setString(5, ccType);
	stmt.setDate(6, dt1);
	stmt.executeUpdate();
	con.close();
    }

    public static void getMerchantTotal(int id) throws IOException,
	    SQLException {

	int len = String.valueOf(id).length();
	if (len == 0)
	    return;

	Connection con = getConnection();
	String sql = "select sum(total) from  PAYMENT where 	merchantId=?";
	PreparedStatement stmt = con.prepareStatement(sql);
	stmt.setInt(1, id);
	ResultSet rs = stmt.executeQuery();
	rs.next();
	double sum = rs.getDouble(1);
	con.close();
	System.out.println(sum);

    }

    public static List<Merchant> getMerchants() throws IOException,
	    SQLException {
	Connection con = getConnection();

	Statement stmt = con.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT * FROM merchant");

	List<Merchant> merchants = new ArrayList<>();

	while (rs.next()) {
	    String name = rs.getString("name");
	    String bankName = rs.getString("bankName");
	    String swift = rs.getString("swift");
	    String account = rs.getString("account");
	    double charge = rs.getDouble("charge");
	    short period = rs.getShort("period");
	    double minSum = rs.getDouble("minSum");
	    double total = rs.getDouble("total");

	    Merchant m = new Merchant(name, bankName, swift, account, charge,
		    period, minSum, total);
	    merchants.add(m);
	}

	con.close();
	return merchants;

    }

    public static void addPayment(Date dt, int merchantId, int customerId,
	    String goods, double total) throws IOException,
	    SQLException {

	Connection con = getConnection();
	
	String sql1 = "Select charge from merchant where id = " + merchantId;
	PreparedStatement stmt = con.prepareStatement(sql1);
	ResultSet rs = stmt.executeQuery();
	rs.next();
	
	double merchantCharge = rs.getDouble("charge");
	double charge = (total*merchantCharge)/100;
	
	//UPDATE payment p SET charge = total * (SELECT charge FROM merchant m WHERE m.id = p.merchantId) / 100.0
	//----------------------------------------------------------------------------------------------------------------
	String sql2 = "INSERT INTO payment (dt, merchantId, ";
	sql2 += " customerId, goods, total, charge) values(";
	sql2 += "?,?,?,?,?,?) ";
	
	
	stmt = con.prepareStatement(sql2);

	java.sql.Date dt1 = new java.sql.Date(dt.getTime());

	stmt.setDate(1, dt1);
	stmt.setInt(2, merchantId);
	stmt.setInt(3, customerId);
	stmt.setString(4, goods);
	stmt.setDouble(5, total);
	stmt.setDouble(6, charge);

	stmt.executeUpdate();	
	//--------------------------------------------------------------------------------
	
	String sql3 = "Select total from merchant where id = " + merchantId;
	stmt = con.prepareStatement(sql3);
	rs = stmt.executeQuery();
	rs.next();
	double merchantTotal = rs.getDouble("total");
	
	merchantTotal += total - charge;
	
	//----------------------------------------------------------------------------------------
	String sql4 = "UPDATE merchant m SET total = " + merchantTotal + " where id  = " + merchantId;
	stmt = con.prepareStatement(sql4);
	stmt.executeUpdate();

	//UPDATE merchant m SET total = (SELECT sum(total - charge)  FROM payment  p WHERE p.merchantId=m.id) 

	rs.close();
	stmt.close();
	con.close();

    }
}
